package com.wonderland.sail.exportexcel;

import com.wonderland.sail.constant.Constant;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 导出excel工具类
 */
public class ExportExcel {
    Logger logger = LoggerFactory.getLogger(this.getClass());
    protected CellStyle styleTitle;
    protected CellStyle styleHead;
    protected CellStyle styleDataC;
    protected CellStyle styleDataR;
    protected CellStyle styleDataL;
    public static final int LEFT = 0;
    public static final int CENTER = 1;
    public static final int RIGHT = 2;
    public static final String Excel2003 = "xls";
    public static final String Excel2007 = "xlsx";
    protected String excelVersion = "xls";

    protected Workbook wb;

    public ExportExcel() {
        this(Excel2003);
    }

    public ExportExcel(String excelVersion) {
        //文件初始化
        if (excelVersion.equals(Excel2003)) {
            wb = new HSSFWorkbook();
            this.excelVersion = Excel2003;
        } else {
            wb = new XSSFWorkbook();
            this.excelVersion = Excel2007;
        }
        styleInit(wb);
    }

    /**
     * @param list               需要导出的list
     * @param customExcelHandler 自定义excel处理
     * @param excelVO            excel对象
     * @param response
     * @return map 包括Workbook，Sheet，OutputStream
     */
    public ExcelVO export(List list, CustomExcelHandler customExcelHandler, ExcelVO excelVO, HttpServletResponse response) {
        try {
            OutputStream os = null;// 取得输出流

            if (response != null) {
                os = response.getOutputStream();
                response.reset();// 清空输出流
                response.setHeader("Content-disposition", "attachment; filename=" + excelVO.getFileName() + new SimpleDateFormat("yyyyMMdd").format(new Date()) + "." + excelVersion);// 设定输出文件头
                response.setContentType("application/msexcel");// 定义输出类型
            }
            //初始化样式
            Sheet sheet = wb.createSheet(excelVO.getTitle());
            //获取列数
            int colNum = excelVO.getColTitle().length;
            //声明合计数组
            Double[] sumArr = new Double[colNum];
            //合并标题单元格
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum - 1));
            //冻结标题级表头
            sheet.createFreezePane(0, excelVO.getTableHeadRowNum() + 1, 0, excelVO.getTableHeadRowNum() + 1);
            //处理标题
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(excelVO.getTitle());
            cell.setCellStyle(styleTitle);
            //处理自定义标题
            customExcelHandler.titleHandler(sheet, styleHead);
            //处理常规标题
            row = sheet.createRow(excelVO.getTableHeadRowNum());
            boolean showSum = false;
            for (int i = 0; i < colNum; i++) {
                sheet.setColumnWidth(i, excelVO.getWidth()[i]);
                cell = row.createCell(i);
                cell.setCellValue(excelVO.getColTitle()[i]);
                cell.setCellStyle(styleHead);
                //初始化合计数组
                sumArr[i] = 0d;
                //判断是否有合计功能
                if (excelVO.getSumFlag() != null) {
                    showSum = showSum || excelVO.getSumFlag()[i];
                }
            }
            //处理数据
            if (list != null && list.size() > 0) {
                for (int i = 0, ii = list.size(); i < ii; i++) {
                    row = sheet.createRow(i + excelVO.getTableHeadRowNum() + 1);
                    //创建表格并加入样式
                    for (int j = 0; j < colNum; j++) {
                        switch (excelVO.getAlign()[j]) {
                            case LEFT:
                                row.createCell(j).setCellStyle(styleDataL);
                                break;
                            case CENTER:
                                row.createCell(j).setCellStyle(styleDataC);
                                break;
                            case RIGHT:
                                row.createCell(j).setCellStyle(styleDataR);
                                break;
                        }
                    }
                    //自定义数据处理
                    customExcelHandler.rowHandler(list.get(i), row);
                    //计算合计，此处需注意，需要合计的列必须为数字
                    try {
                        if (showSum) {
                            for (int j = 0; j < colNum; j++) {
                                if (excelVO.getSumFlag()[j]) {
                                    sumArr[j] += row.getCell(j).getNumericCellValue();
                                }
                            }
                        }
                    } catch (Exception e) {
                        logger.error(Constant.ERROR, e);
                    }
                }
                //如果有合计则显示合计
                if (showSum) {
                    row = sheet.createRow(list.size() + excelVO.getTableHeadRowNum() + 1);
                    for (int i = 0; i < colNum; i++) {
                        cell = row.createCell(i);
                        if (i == 0) {
                            cell.setCellValue("合计");
                        } else {
                            if (excelVO.getSumFlag()[i]) {
                                cell.setCellValue(sumArr[i]);
                            }
                        }
                        cell.setCellStyle(styleDataR);
                    }
                }
            }
            excelVO.setWorkbook(wb);
            excelVO.setSheet(sheet);
            excelVO.setOutputStream(os);
        } catch (Exception e) {
            logger.error(Constant.ERROR, e);
        }
        return excelVO;
    }

    /**
     * 解析html导出excel
     *
     * @param excelVO ExcelVO
     * @return dwr的下载文件
     */
    public Workbook exportExcelByHtml(ExcelVO excelVO) {
        try {
            int[] col = new int[excelVO.getColNum()]; //保存行合并的数组，标志若为0则表示没有行合并，需要显示，反之则表示此行已被合并
            CellStyle[] colStyle = new CellStyle[excelVO.getColNum()]; //保存每列的样式。
            // 创建Excel的工作sheet,对应到一个excel文档的tab
            Sheet sheet = wb.createSheet("sheet1");
            sheet.createFreezePane(0, excelVO.getTableHeadRowNum(), 0, excelVO.getTableHeadRowNum());  // 冻结标题及表头
            Row row;
            Cell cell;
            Document doc = Jsoup.parseBodyFragment(excelVO.getHtmlStr());
            Element body = doc.body();
            Elements trs = body.select("tr");
            Elements tds;
            Element td;
            //行循环
            int tempRow = 0;
            for (int i = 0, ii = trs.size(); i < ii; i++) {
                tds = trs.get(i).select("th");
                if (tds.size() == 0) {//列表表头
                    tds = trs.get(i).select("td");
                }
                if (tds.size() != 0) { //当为空行的时候越过
                    row = sheet.createRow(tempRow);
                    tempRow++;
                    //列循环
                    for (int j = 0, tdIndex = 0; j < excelVO.getColNum(); j++) {
                        //如果当前有行合并，则创建空单元格
                        if (col[j] != 0) {
                            cell = row.createCell(j);
                            cell.setCellValue("");
                            styleHandler(cell, colStyle, null, i, j, excelVO.getTableHeadRowNum()); //合并行
                            col[j]--;
                            continue;
                        }
                        cell = row.createCell(j);//创建单元格
                        td = tds.get(tdIndex++);//获取td
                        if (!"businessId".equals(td.attr("businessId"))) {
                            //此处判断是为了防止类似“512D”这种情况，Double.parseDouble("512D")的结果是512，故加此判断
                            if (td.text().toLowerCase().endsWith("d") || td.text().toLowerCase().endsWith("f")) {
                                cell.setCellValue(td.text());//将td中文字填入单元格
                            } else {
                                try {
                                    cell.setCellValue(Double.parseDouble(td.text().replace(",", "")));
                                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                } catch (Exception e) {
                                    if (!"".equals(td.text())) {
                                        cell.setCellValue(td.text());//将td中文字填入单元格
                                    }
                                }
                            }
                        } else {
                            cell.setCellValue(td.text());//将td中文字填入单元格
                        }
                        styleHandler(cell, colStyle, td, i, j, excelVO.getTableHeadRowNum()); //处理单元格样式
                        String colspan = td.attr("colspan");//获取列合并数
                        int oriJ = j;
                        if (!colspan.equals("")) {
                            sheet.addMergedRegion(new CellRangeAddress(i, i, j, j + Integer.parseInt(colspan) - 1)); //合并列
                            int temp = j;
                            //循环创建空的单元格并设置边框的样式
                            for (j++; j <= (temp + Integer.parseInt(colspan) - 1); j++) {
                                cell = row.createCell(j);
                                cell.setCellValue("");
                                styleHandler(cell, colStyle, null, i, j, excelVO.getTableHeadRowNum());
                            }
                            j--;
                        }
                        String rowspan = td.attr("rowspan");
                        if (!rowspan.equals("")) {
                            if (colspan.equals("")) {
                                col[j] = Integer.parseInt(rowspan) - 1;
                                sheet.addMergedRegion(new CellRangeAddress(i, i + Integer.parseInt(rowspan) - 1, j, j));
                            } else {
                                for (int m = 0, n = Integer.parseInt(colspan); m < n; m++) {
                                    col[oriJ + m] = Integer.parseInt(rowspan) - 1;
                                }
                                sheet.addMergedRegion(new CellRangeAddress(i, i + Integer.parseInt(rowspan) - 1, oriJ, oriJ + Integer.parseInt(colspan) - 1));
                            }
                        }
                    }
                }
            }
            for (int i = 0; i < excelVO.getColNum(); i++) {
                sheet.autoSizeColumn(i, true);
                sheet.setColumnWidth(i, (short) (sheet.getColumnWidth(i) + 500));
            }
            return wb;
        } catch (Exception e) {
            logger.error(Constant.ERROR, e);
            return null;
        }
    }

    public Cell styleHandler(Cell cell, CellStyle[] colStyle, Element td, int currentRow, int currentCol, int titleRowNum) {

        //第一行设为标题
        if (currentRow == 0) {
            cell.setCellStyle(styleTitle);
            //表头样式
        } else if (currentRow < titleRowNum) {
            cell.setCellStyle(styleHead);
            //第一行数据时记录样式，读取html的align属性，记录相应的对齐方式
        } else if (currentRow == titleRowNum) {
            if (td != null) {
                String align = td.attr("align");
                if ("left".equals(align) || "".endsWith(align)) {
                    cell.setCellStyle(styleDataL);
                    colStyle[currentCol] = styleDataL;
                } else if ("center".equals(align) || "middle".equals(align)) {
                    cell.setCellStyle(styleDataC);
                    colStyle[currentCol] = styleDataC;
                } else if ("right".equals(align)) {
                    cell.setCellStyle(styleDataR);
                    colStyle[currentCol] = styleDataR;
                }
            } else {
                //若传入的td为null则默认将这个单元格居中处理。
                cell.setCellStyle(styleDataC);
                colStyle[currentCol] = styleDataC;
            }
        } else {
            //其他行数据样式以第一行为标准
            cell.setCellStyle(colStyle[currentCol]);
        }
        return cell;
    }

    protected void styleInit(Workbook wb) {
        // --------------------标题样式------------------
        // 创建字体样式 - 标题
        Font fontTitle = wb.createFont();
        fontTitle.setBoldweight((short) 10);
        fontTitle.setFontHeight((short) 30);
        fontTitle.setColor(HSSFColor.BLACK.index);
        fontTitle.setFontHeightInPoints((short) 10);
        fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 创建标题单元格样式
        styleTitle = wb.createCellStyle();
        styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        styleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleTitle.setBorderRight(CellStyle.BORDER_THIN);
        styleTitle.setFont(fontTitle);// 设置字体

        // --------------表头样式-----------------
        Font fontHead = wb.createFont();
        fontHead.setFontHeightInPoints((short) 10);
        fontHead.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // 创建表头单元格样式
        styleHead = wb.createCellStyle();
        styleHead.setAlignment(CellStyle.ALIGN_CENTER);
        styleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 设置边框
        styleHead.setBorderBottom(CellStyle.BORDER_THIN);
        styleHead.setBorderLeft(CellStyle.BORDER_THIN);
        styleHead.setBorderRight(CellStyle.BORDER_THIN);
        styleHead.setBorderTop(CellStyle.BORDER_THIN);
        styleHead.setFont(fontHead);// 设置字体
        styleHead.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);     // 为表头添加背景颜色
        styleHead.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // ----------------创建数据字体样式-----------------
        Font fontData = wb.createFont();
        fontData.setFontHeightInPoints((short) 10);

        // -------------------创建单元格样式 居中----------------
        styleDataC = wb.createCellStyle();
        styleDataC.setAlignment(CellStyle.ALIGN_CENTER);
        styleDataC.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDataC.setBorderBottom(CellStyle.BORDER_THIN);
        styleDataC.setBorderLeft(CellStyle.BORDER_THIN);
        styleDataC.setBorderRight(CellStyle.BORDER_THIN);
        styleDataC.setBorderTop(CellStyle.BORDER_THIN);
        styleDataC.setFont(fontData);// 设置字体
        // -------------------创建单元格样式 居右----------------
        styleDataR = wb.createCellStyle();
        styleDataR.setAlignment(CellStyle.ALIGN_RIGHT);
        styleDataR.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDataR.setBorderBottom(CellStyle.BORDER_THIN);
        styleDataR.setBorderLeft(CellStyle.BORDER_THIN);
        styleDataR.setBorderRight(CellStyle.BORDER_THIN);
        styleDataR.setBorderTop(CellStyle.BORDER_THIN);
        styleDataR.setFont(fontData);// 设置字体
        // -------------------创建单元格样式 居左----------------
        styleDataL = wb.createCellStyle();
        styleDataL.setAlignment(CellStyle.ALIGN_LEFT);
        styleDataL.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDataL.setBorderBottom(CellStyle.BORDER_THIN);
        styleDataL.setBorderLeft(CellStyle.BORDER_THIN);
        styleDataL.setBorderRight(CellStyle.BORDER_THIN);
        styleDataL.setBorderTop(CellStyle.BORDER_THIN);
        styleDataL.setFont(fontData);// 设置字体
    }
}
